package com.finance.cooperate.dao.repository.primary;

import com.finance.cooperate.dao.entity.primary.Sms;
import com.finance.cooperate.dao.support.jpa.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @InterfaceName SmsRepository
 * @Description 短信仓库
 * @Author shen
 * @Date 2023/8/6 21:23
 * @Modify ...
 */
@Repository
public interface SmsRepository extends BaseRepository<Sms, Long> {

    // 先查询最后一次上报时间
    @Query(value = "SELECT MAX(create_time) AS max_create_time FROM p_current_sms WHERE device_id = ?1 AND package_name = ?2 AND create_time <= ?3", nativeQuery = true)
    Date getSmsLastTime(String deviceId, String packageName, Date lastSubmitTime);

    /**
     * @Author shen
     * @Description 最近3个月数据
     * @Date 15:34 2023/7/20
     * @Param [deviceId, packageName, lastTime]
     * @return java.util.List<com.finance.cooperate.dao.entity.primary.PCurrentSms>
     **/
    @Query(value = "SELECT MAX(a.id) AS id, a.phone, a.opposite_phone, a.content, a.sms_time, a.type, a.user_id, a.device_id, a.package_name, a.`read`, a.create_time, a.update_time FROM p_current_sms a WHERE a.device_id = ?1 AND a.package_name = ?2 AND a.create_time = ?3 AND DATEDIFF(a.create_time, str_to_date(a.sms_time, '%Y-%m-%d %H:%i:%s')) <= 3 * 30 GROUP BY a.phone, a.opposite_phone, a.content, a.sms_time, a.type, a.user_id, a.device_id, a.package_name, a.`read`, a.create_time, a.update_time", nativeQuery = true)
    List<Sms> getSms3Month(String deviceId, String packageName, Date lastTime);




    // 获取进件前最后一次上报数据【临时，老数据没有贷款次数】
    @Query(value = "SELECT MAX(a.id) AS id, a.phone, a.opposite_phone, a.content, a.sms_time, a.type, a.user_id, a.device_id, a.package_name, a.`read`, a.create_time, a.update_time FROM p_current_sms a WHERE a.device_id = ?1 AND a.package_name = ?2 AND a.sms_time < ?3 AND a.loan_num = 0 AND a.loan_status = 0 AND DATEDIFF(a.create_time, str_to_date(a.sms_time, '%Y-%m-%d %H:%i:%s')) <= 3 * 30 GROUP BY a.phone, a.opposite_phone, a.content, a.sms_time, a.type, a.user_id, a.device_id, a.package_name, a.`read`, a.create_time, a.update_time", nativeQuery = true)
    List<Sms> getFirstSms3Month(String deviceId, String packageName, Date lastSubmitTime);

//    @Query(value = "SELECT MAX(a.id) AS id, a.phone, a.opposite_phone, a.content, a.sms_time, a.type, a.user_id, a.device_id, a.package_name, a.`read`, a.create_time, a.update_time FROM p_current_sms a WHERE a.device_id = ?1 AND a.package_name = ?2 AND a.sms_time < ?3 AND DATEDIFF(a.create_time, str_to_date(a.sms_time, '%Y-%m-%d %H:%i:%s')) <= 3 * 30 GROUP BY a.phone, a.opposite_phone, a.content, a.sms_time, a.type, a.user_id, a.device_id, a.package_name, a.`read`, a.create_time, a.update_time", nativeQuery = true)
//    List<Sms> getFirstSms3Month(String deviceId, String packageName, Date lastSubmitTime);


}
